﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class SubsidiaryDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Get all subsidiaries
        /// </summary>
        /// <returns>List of subsidiaries</returns>
        public static List<Subsidiary> GetAllSubsidiaries()
        {
            var subsidiaries = new List<Subsidiary>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetAllSubsidiary", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var subsidiary = new Subsidiary
                        {
                            SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]),
                            CityId = Convert.ToByte(reader["CityId"])
                        };
                        subsidiaries.Add(subsidiary);
                    }
                }
            }
            return subsidiaries;
        }

        /// <summary>
        ///     Get city id by subsidiary id
        /// </summary>
        /// <param name="subsidiaryId">Represent's the subsidiary id</param>
        /// <returns>City id</returns>
        public static byte GetCityIdBySubsidiaryId(int subsidiaryId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetCityBySubsidiaryId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@SubsidiaryId", subsidiaryId);

                connection.Open();
                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        /// <summary>
        ///     Get all subsidiaries
        /// </summary>
        /// <returns>List of subsidiaries</returns>
        public static List<Subsidiary> GetAllSubsidiariesWithName()
        {
            var subsidiaries = new List<Subsidiary>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetAllSubsidiaryWithName", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var subsidiary = new Subsidiary
                        {
                            SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]),
                            BranchName = reader["Name"].ToString()
                        };
                        subsidiaries.Add(subsidiary);
                    }
                }
            }
            return subsidiaries;
        }

        /// <summary>
        ///     Get all subsidiaries under a district
        /// </summary>
        /// <param name="districtId">Represent's the district Id</param>
        /// <returns>List of subsidiaries</returns>
        public static List<Subsidiary> GetAllSubsidiariesWithName(int districtId)
        {
            var subsidiaries = new List<Subsidiary>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetSubsidiariesByDistrictId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@DistrictId", districtId);

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var subsidiary = new Subsidiary
                        {
                            SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]),
                            BranchName = reader["Name"].ToString()
                        };
                        subsidiaries.Add(subsidiary);
                    }
                }
            }
            return subsidiaries;
        }
    }
}